﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using com.loto.Common.DBUtility;
using System.Data.SqlClient;
using com.huigo.Framework.Model;


namespace com.huigo.Framework.BLL
{
    public class OrderInfo
    {
        public static bool AddOrderInfo(Model.OrderInfo orderInfo)
        {
            return SqlServerDal.OrderInfo.Insert(orderInfo, null);
        }


        public static List<Model.OrderInfo> GetOrderInfo(string orderNO, DateTime orderStart, DateTime orderEnd, string policyNo, string policyInsName, string custName, int orderStatus, long insCompanyId, long proType, int pageSize, int pageIndex)
        {
            List<string> fields = new List<string>() 
             {
                "ORDER_ID",
                "ORDER_CUST_FID",
                "ORDER_PRODUCT_FID",
                "ORDER_INS_TYPE_FID",
                "ORDER_NO",
                "ORDER_AMOUNT",
                "ORDER_PAY_AMOUNT",
                "ORDER_DISCOUNT_AMOUNT",
                "ORDER_SCORE_AMOUNT",
                "ORDER_CREATED_ON",
                "ORDER_PAID_ON",
                "ORDER_LAST_UPDATED_ON",
                "ORDER_SOURCE",
                "ORDER_FROM",
                "ORDER_STATUS_FID",
                "ORDER_PERSON_COUNT",
                "CUST_NAME",
                "STATUS_DESC",
                "ORDER_INS_COMPANY_NAME",
                "ORDER_PRODUCT_NAME",
                 "PRODUCT_NAME",
                 "PRODUCT_DESC",
                 "PRODUCT_SERVICE_DESC",
                 "PRODUCT_STATUS"
             };
            List<JoinStatement> joinList = new List<JoinStatement>();
            joinList.Add(new JoinStatement("LEFT", "[HG_PRODUCT_INFO]", "ORDER_PRODUCT_FID=PRODUCT_ID"));
            joinList.Add(new JoinStatement("LEFT", "[HG_CUST_INFO]", "ORDER_CUST_FID=CUST_ID"));
            joinList.Add(new JoinStatement("LEFT", "[HG_ORDER_STATUS]", "ORDER_STATUS_FID=STATUS_ID"));
            List<OrderByStatement> OrderByStatement = new List<OrderByStatement>() 
             {
                 new OrderByStatement("ORDER_CREATED_ON",false)
             };
            List<WhereStatement> whereList = new List<WhereStatement>();
            if (!string.IsNullOrEmpty(orderNO))
            {
                whereList.Add(new WhereStatement("ORDER_NO", "=", "'" + orderNO + "'", "AND"));
            }
            if (orderStart != DateTime.MinValue && orderEnd != DateTime.MinValue)
            {
                whereList.Add(new WhereStatement("ORDER_CREATED_ON", "BETWEEN", "'" + orderStart + "' AND '" + orderEnd + "'", "AND"));
            }
            if (orderStatus > -1)
            {
                whereList.Add(new WhereStatement("ORDER_STATUS_FID", "=", orderStatus.ToString(), "AND"));
            }
            if (insCompanyId != long.MinValue)
            {
                whereList.Add(new WhereStatement("PRODUCT_INS_FID", "=", insCompanyId.ToString(), "AND"));
            }
            if (proType > -1)
            {
                whereList.Add(new WhereStatement("PRODUCT_TYPE_FID", "=", proType.ToString(), "AND"));
            }
            if (!string.IsNullOrEmpty(custName))
            {
                whereList.Add(new WhereStatement("CUST_NAME", "like", "'%" + custName + "%'", "AND"));
            }

            if (!string.IsNullOrEmpty(policyInsName) || !string.IsNullOrEmpty(policyNo))
            {
                List<string> q = new List<string>();
                if (!string.IsNullOrEmpty(policyInsName))
                    q.Add("POLICY_INSURED_NAME = '" + policyInsName + "'");
                if (!string.IsNullOrEmpty(policyNo))
                    q.Add("POLICY_NO = '" + policyNo + "'");
                string longQ = string.Join("AND", q.ToArray());
                whereList.Add(new WhereStatement("ORDER_ID", "IN", "(SELECT POLICY_ORDER_FID FROM HG_POLICY_INFO_AIR WHERE " + longQ + " UNION ALL SELECT POLICY_ORDER_FID FROM HG_POLICY_INFO_TRAVEL WHERE " + longQ + ")", "AND"));
            }

            if (whereList.Count == 0)
            {
                whereList.Add(new WhereStatement("ORDER_CREATED_ON", "BETWEEN", "'" + DateTime.Now.Date + "' AND '" + DateTime.Now.Date.AddDays(1) + "'", "AND"));
            }
            whereList.Add(new WhereStatement("PRODUCT_STATUS", "=", ((int)Dict.IsActive.Yes).ToString(), ""));
            List<Model.OrderInfo> orderList = null;
            int recordCount = 0;
            int pageCount = 0;
            orderList = SqlServerDal.OrderInfo.GetItemsByProc(fields, joinList, whereList, OrderByStatement, "[PROC_PAGINATION_OUT]", pageSize, pageIndex, out recordCount, out pageCount, null);
            orderList.ForEach(delegate(Model.OrderInfo info)
            {
                info.RecordCount = recordCount;
                info.PageCount = pageCount;
            });
            return orderList;

        }

        public static Model.OrderInfo GetOrderInfoById(long id)
        {
            Model.OrderInfo orderInfo = new Model.OrderInfo();
            orderInfo.OrderId = id;
            orderInfo.ProdcutStatus = 1;
            List<string> fields = new List<string>() 
             {  "ORDER_ID",
                "ORDER_CUST_FID",
                "ORDER_PRODUCT_FID",
                "ORDER_INS_TYPE_FID",
                "ORDER_NO",
                "ORDER_AMOUNT",
                "ORDER_PAY_AMOUNT",
                "ORDER_DISCOUNT_AMOUNT",
                "ORDER_SCORE_AMOUNT",
                "ORDER_CREATED_ON",
                "ORDER_PAID_ON",
                "ORDER_LAST_UPDATED_ON",
                "ORDER_SOURCE",
                "ORDER_FROM",
                " ORDER_STATUS_FID",
                "CUST_NAME",
                "STATUS_DESC",
                "ORDER_INS_COMPANY_NAME",
                "ORDER_PRODUCT_NAME",
                 "PRODUCT_NAME",
                 "PRODUCT_DESC",
                 "PRODUCT_SERVICE_DESC",
                 "PRODUCT_STATUS,ORDER_PRODUCT_CODE,PRODUCT_TIME_DESC"
             };
            List<JoinStatement> joinList = new List<JoinStatement>()
            {
                 new JoinStatement("LEFT","[HG_PRODUCT_INFO]","ORDER_PRODUCT_FID=PRODUCT_ID"),
                new JoinStatement("LEFT","[HG_CUST_INFO]","ORDER_CUST_FID=CUST_ID"),
                new JoinStatement("LEFT","[HG_ORDER_STATUS]","ORDER_STATUS_FID=STATUS_ID")
            };
            List<WhereStatement> whereList = new List<WhereStatement>();
            return SqlServerDal.OrderInfo.GetItem(orderInfo, fields, joinList, null);
        }

        public static bool UpdateOrderInfo(long OrderID, Model.OrderInfo orderinfo)
        {
            List<WhereStatement> whereList = new List<WhereStatement>()
            {
                    new WhereStatement("ORDER_ID","=",OrderID.ToString(),"")
            };
            return SqlServerDal.OrderInfo.Update(orderinfo, whereList, null);
        }

        public static bool UpdateOrderInfo(long OrderID, Model.OrderInfo orderinfo, SqlTransaction trans)
        {
            List<WhereStatement> whereList = new List<WhereStatement>()
            {
                    new WhereStatement("ORDER_ID","=",OrderID.ToString(),"")
            };
            return SqlServerDal.OrderInfo.Update(orderinfo, whereList, trans);
        }

        public static bool OrderInfoIsExisted(long cid, int orderStaus)
        {
            bool isExist = false;
            List<Model.OrderInfo> orderInfo = new List<Model.OrderInfo>();
            List<string> fields = new List<string>() 
            {
                "ORDER_ID",
            };
            List<WhereStatement> whereList = new List<WhereStatement>()
            {
                new WhereStatement("ORDER_ID","=",cid.ToString(),"AND "),
                new WhereStatement("ORDER_STATUS_FID","=","'"+orderStaus+"'","")
            };
            orderInfo = SqlServerDal.OrderInfo.GetItems(fields, whereList, null, null);
            if (orderInfo != null && orderInfo.Count > 0)
            {
                isExist = true;
            }
            return isExist;
        }
        public static bool UpdateOrderInfoStatus(long OrderID, int OrderStatusFid, long userID)
        {
            List<WhereStatement> whereList = new List<WhereStatement>()
            {
                    new WhereStatement("ORDER_ID","=",OrderID.ToString(),"")
            };
            Model.OrderInfo order = new Model.OrderInfo();
            order.OrderStatusFid = OrderStatusFid;
            int StatusBefore = 0;
            StatusBefore = GetOrderInfoById(OrderID).OrderStatusFid;
            if (SqlServerDal.OrderInfo.Update(order, whereList, null))
            {
                BLL.OrderFlow.AddOrderFlow(OrderID, userID, OrderStatusFid, com.loto.Common.DictEnum.GetDictValueByKey(Dict.DictOrderStatus, OrderStatusFid), null);
                return true;
            }
            return false;

        }
        /// <summary>
        /// 提交订单
        /// </summary>
        /// <param name="orderCustFid"></param>
        /// <param name="productId"></param>
        /// <param name="type"></param>
        /// <param name="companyId"></param>
        /// <param name="orderAmount"></param>
        /// <param name="payAmount"></param>
        /// <returns></returns>
        public static long InsertOrderInfo(long orderCustFid, long productId, string policyInfo, int source)
        {
            long orderId = long.MinValue;
            decimal totalPrice = 0;
            //取得产品信息
            Model.ProductInfo productInfo = BLL.ProductInfo.GetOrderProductInfoById(productId);
            if (productInfo.CompanyId != long.MinValue)
            {
                string orderName = productInfo.CompanyEx + Others.GetIndex();
                decimal productPrice = productInfo.ProductSettlePrice;
                Model.OrderInfo orderInfo = new Model.OrderInfo();
                orderInfo.OrderCustFid = orderCustFid;
                orderInfo.OrderProductFid = productId;
                orderInfo.OrderProductName = productInfo.ProductName;
                orderInfo.OrderInsCompanyName = productInfo.CompanyName;
                orderInfo.OrderSupName = productInfo.SupName;
                orderInfo.OrderInsTypeFid = productInfo.ProductTypeFid;
                orderInfo.OrderNo = orderName;
                orderInfo.OrderAmount = totalPrice;
                orderInfo.OrderPayAmount = totalPrice;
                orderInfo.OrderDiscountAmount = 0;
                orderInfo.OrderScoreAmount = 0;
                orderInfo.OrderCreatedOn = DateTime.Now;
                orderInfo.OrderLastUpdatedOn = DateTime.Now;
                orderInfo.OrderFrom = Convert.ToByte(source);
                orderInfo.OrderSource = Convert.ToByte(source);
                orderInfo.OrderStatusFid = (int)Dict.OrderStatus.UnPay;
                orderInfo.OrderProductCode = productInfo.ProductCode;
                int personCount = 0;
                using (SqlTransaction trans = SqlHelper.SQLTransaction(SqlHelper.MainSQLConnString))
                {
                    try
                    {
                        orderId = SqlServerDal.OrderInfo.AddOrder(orderInfo, trans);
                        if (orderId > 0)
                        {
                            BLL.OrderFlow.AddOrderFlow(orderId, 0, (int)Dict.OrderStatus.UnPay, com.loto.Common.DictEnum.GetDictValueByKey(Dict.DictOrderStatus, (int)Dict.OrderStatus.UnPay), trans);
                            //判断险种
                            bool isInsertPolicySuc = false;
                            switch (productInfo.ProductTypeFid)
                            {
                                case (int)Dict.InsType.Air:
                                    personCount = PolicyAir.InsertPolicy(PolicyAir.GetPolicyList(policyInfo, productInfo.ProductSettlePrice, out totalPrice), orderCustFid, orderId, orderInfo.OrderInsTypeFid, trans);
                                    isInsertPolicySuc = personCount > 0;
                                    break;
                                case (int)Dict.InsType.Travel:
                                    personCount = PolicyTravel.InsertPolicy(PolicyTravel.GetPolicyList(policyInfo, productInfo.ProductSettlePrice, out totalPrice), orderCustFid, orderId, orderInfo.OrderInsTypeFid, trans);
                                    isInsertPolicySuc = personCount > 0;
                                    break;
                            }
                            if (isInsertPolicySuc)
                            {
                                Model.OrderInfo oInfo = new Model.OrderInfo();
                                oInfo.OrderAmount = totalPrice;
                                oInfo.OrderPayAmount = totalPrice;
                                oInfo.OrderPersonCount = personCount;
                                UpdateOrderInfo(orderId, oInfo, trans);
                                trans.Commit();
                            }
                            else
                                trans.Rollback();
                        }
                        else
                            trans.Rollback();
                    }
                    catch
                    {
                        trans.Rollback();
                        return long.MinValue;
                    }
                }
            }
            return orderId;
        }
        /// <summary>
        /// 根据订单ID查询订单信息
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public static Model.OrderInfo getOrderNoByOrderId(long orderId)
        {
            Model.OrderInfo orderInfo = new com.huigo.Framework.Model.OrderInfo();
            orderInfo.OrderId = orderId;
            List<string> fileds = new List<string>()
           {
               "ORDER_ID",
               "ORDER_NO",
               "ORDER_CUST_FID",
               "ORDER_PAY_AMOUNT"
           };
            return SqlServerDal.OrderInfo.GetItem(orderInfo, fileds, null, null);
        }

        public static Model.OrderInfo GetUnpayOrderInfoById(long custId, long orderId)
        {
            Model.OrderInfo orderInfo = new Model.OrderInfo();
            orderInfo.OrderId = orderId;
            orderInfo.OrderCustFid = custId;
            orderInfo.OrderStatusFid = (int)Dict.OrderStatus.UnPay;
            List<string> fields = new List<string>() 
            {   
                "ORDER_ID",
                "ORDER_NO",
                "ORDER_PAY_AMOUNT",
                "ORDER_INS_TYPE_FID",
                "ORDER_PRODUCT_CODE"
            };
            return SqlServerDal.OrderInfo.GetItem(orderInfo, fields, null, null);
        }
        /// <summary>
        /// 前台查询订单列表
        /// </summary>
        /// <param name="orderNO">订单ID</param>
        /// <param name="orderStart">订单开始时间</param>
        /// <param name="orderEnd">结束时间</param>
        /// <param name="orderStatus">订单状态</param>
        /// <param name="insCompanyId">保险公司ID</param>
        /// <param name="proType">产品类型</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public static List<Model.OrderInfo> getOrderInfoList(string orderNO, DateTime orderStart, DateTime orderEnd, int orderStatus, long insCompanyId, long proType, long custFid, int pageSize, int pageIndex)
        {
            List<string> fields = new List<string>() 
             {
                "ORDER_ID",
                "ORDER_CUST_FID",
                "ORDER_PRODUCT_FID",
                "ORDER_INS_TYPE_FID",
                "ORDER_NO",
                "ORDER_AMOUNT",
                "ORDER_PAY_AMOUNT",
                "ORDER_DISCOUNT_AMOUNT",
                "ORDER_SCORE_AMOUNT",
                "ORDER_CREATED_ON",
                "ORDER_PAID_ON",
                "ORDER_LAST_UPDATED_ON",
                "ORDER_SOURCE",
                "ORDER_FROM",
                "ORDER_STATUS_FID",
                "ORDER_PERSON_COUNT",
                "CUST_NAME",
                "STATUS_DESC",
                "ORDER_INS_COMPANY_NAME",
                "ORDER_PRODUCT_NAME",
                 "PRODUCT_NAME",
                 "PRODUCT_DESC",
                 "PRODUCT_SERVICE_DESC",
                 "PRODUCT_STATUS"
             };
            List<JoinStatement> joinList = new List<JoinStatement>();
            joinList.Add(new JoinStatement("LEFT", "[HG_PRODUCT_INFO]", "ORDER_PRODUCT_FID=PRODUCT_ID"));
            joinList.Add(new JoinStatement("LEFT", "[HG_CUST_INFO]", "ORDER_CUST_FID=CUST_ID"));
            joinList.Add(new JoinStatement("LEFT", "[HG_ORDER_STATUS]", "ORDER_STATUS_FID=STATUS_ID"));
            List<OrderByStatement> OrderByStatement = new List<OrderByStatement>() 
             {
                 new OrderByStatement("ORDER_CREATED_ON",false)
             };
            List<WhereStatement> whereList = new List<WhereStatement>();
            if (!string.IsNullOrEmpty(orderNO))
            {
                whereList.Add(new WhereStatement("ORDER_NO", "=", "'" + orderNO + "'", "AND"));
            }
            if (orderStart != DateTime.MinValue && orderEnd != DateTime.MinValue)
            {
                whereList.Add(new WhereStatement("ORDER_CREATED_ON", "BETWEEN", "'" + orderStart + "' AND '" + orderEnd + "'", "AND"));
            }
            if (orderStatus > -1)
            {
                whereList.Add(new WhereStatement("ORDER_STATUS_FID", "=", orderStatus.ToString(), "AND"));
            }
            if (insCompanyId != long.MinValue)
            {
                whereList.Add(new WhereStatement("PRODUCT_INS_FID", "=", insCompanyId.ToString(), "AND"));
            }
            if (proType > -1)
            {
                whereList.Add(new WhereStatement("PRODUCT_TYPE_FID", "=", proType.ToString(), "AND"));
            }
            if (whereList.Count == 0)
            {
                whereList.Add(new WhereStatement("ORDER_CREATED_ON", "BETWEEN", "'" + DateTime.Now.Date + "' AND '" + DateTime.Now.Date.AddDays(1) + "'", "AND"));
            }
            if (custFid > 0)
            {
                whereList.Add(new WhereStatement("ORDER_CUST_FID", "=", custFid.ToString(), "AND"));
            }
            whereList.Add(new WhereStatement("PRODUCT_STATUS", "=", ((int)Dict.IsActive.Yes).ToString(), ""));
            List<Model.OrderInfo> orderList = null;
            int recordCount = 0;
            int pageCount = 0;
            orderList = SqlServerDal.OrderInfo.GetItemsByProc(fields, joinList, whereList, OrderByStatement, "[PROC_PAGINATION_OUT]", pageSize, pageIndex, out recordCount, out pageCount, null);
            orderList.ForEach(delegate(Model.OrderInfo info)
            {
                info.RecordCount = recordCount;
                info.PageCount = pageCount;
            });
            return orderList;
        }
        /// <summary>
        /// 查询未付款订单
        /// </summary>
        /// <param name="custFid">用户ID</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public static List<Model.OrderInfo> getUnpaidOrderList(long custFid, int pageSize, int pageIndex)
        {
            List<string> fields = new List<string>() 
             {
                "ORDER_ID",
                "ORDER_CUST_FID",
                "ORDER_PRODUCT_FID",
                "ORDER_INS_TYPE_FID",
                "ORDER_NO",
                "ORDER_AMOUNT",
                "ORDER_PAY_AMOUNT",
                "ORDER_DISCOUNT_AMOUNT",
                "ORDER_SCORE_AMOUNT",
                "ORDER_CREATED_ON",
                "ORDER_PAID_ON",
                "ORDER_LAST_UPDATED_ON",
                "ORDER_SOURCE",
                "ORDER_FROM",
                "ORDER_STATUS_FID",
                "ORDER_PERSON_COUNT",
                "CUST_NAME",
                "STATUS_DESC",
                "ORDER_INS_COMPANY_NAME",
                "ORDER_PRODUCT_NAME",
                 "PRODUCT_NAME",
                 "PRODUCT_DESC",
                 "PRODUCT_SERVICE_DESC",
                 "PRODUCT_STATUS"
             };
            List<JoinStatement> joinList = new List<JoinStatement>();
            joinList.Add(new JoinStatement("LEFT", "[HG_PRODUCT_INFO]", "ORDER_PRODUCT_FID=PRODUCT_ID"));
            joinList.Add(new JoinStatement("LEFT", "[HG_CUST_INFO]", "ORDER_CUST_FID=CUST_ID"));
            joinList.Add(new JoinStatement("LEFT", "[HG_ORDER_STATUS]", "ORDER_STATUS_FID=STATUS_ID"));
            List<OrderByStatement> OrderByStatement = new List<OrderByStatement>() 
            {
                 new OrderByStatement("ORDER_CREATED_ON",false)
            };
            List<WhereStatement> whereList = new List<WhereStatement>();
            if (custFid > 0)
            {
                whereList.Add(new WhereStatement("ORDER_CUST_FID", "=", custFid.ToString(), "AND"));
            }
            whereList.Add(new WhereStatement("ORDER_STATUS_FID", "=", ((int)Dict.OrderStatus.UnPay).ToString(), "and"));
            whereList.Add(new WhereStatement("PRODUCT_STATUS", "=", ((int)Dict.IsActive.Yes).ToString(), ""));
            List<Model.OrderInfo> orderList = null;
            int recordCount = 0;
            int pageCount = 0;
            orderList = SqlServerDal.OrderInfo.GetItemsByProc(fields, joinList, whereList, OrderByStatement, "[PROC_PAGINATION_OUT]", pageSize, pageIndex, out recordCount, out pageCount, null);
            orderList.ForEach(delegate(Model.OrderInfo info)
            {
                info.RecordCount = recordCount;
                info.PageCount = pageCount;
            });
            return orderList;
        }
        /// <summary>
        /// 用户取消订单
        /// </summary>
        /// <param name="orderId">订单ID</param>
        /// <param name="custFid">用户ID</param>
        /// <returns></returns>
        public static bool CancelOrderInfo(long orderId, long custFid)
        {
            bool result = false;
            Model.OrderInfo orderInfo = new com.huigo.Framework.Model.OrderInfo();
            List<string> fileds = new List<string>()
            {
                "ORDER_ID",
                "ORDER_CUST_FID",
                "ORDER_STATUS_FID",
                "ORDER_INS_TYPE_FID"
            };
            orderInfo.OrderId = orderId;
            orderInfo.OrderCustFid = custFid;
            Model.OrderInfo order = SqlServerDal.OrderInfo.GetItem(orderInfo, fileds, null, null);
            if (order != null && order.OrderStatusFid == 1)
            {
                Model.CustInfo custInfo = new com.huigo.Framework.Model.CustInfo();
                if (custFid > 0)
                {
                    Model.CustInfo cust = new com.huigo.Framework.Model.CustInfo();
                    cust.CustId = custFid;
                    List<string> filed = new List<string>()
                    {
                        "CUST_NAME"
                    };
                    custInfo = SqlServerDal.CustInfo.GetItem(cust, filed, null, null);
                }
                using (SqlTransaction trans = SqlHelper.SQLTransaction(SqlHelper.MainSQLConnString))
                {
                    Model.OrderInfo orderUpdate = new com.huigo.Framework.Model.OrderInfo();
                    orderUpdate.OrderStatusFid = 0;
                    List<WhereStatement> whereList = new List<WhereStatement>()
                    {
                        new WhereStatement("ORDER_ID","=",""+orderId+"","")
                    };
                    bool updateResult = SqlServerDal.OrderInfo.Update(orderUpdate, whereList, trans);
                    if (updateResult)
                    {
                        Model.OrderFlow orderFlow = new com.huigo.Framework.Model.OrderFlow();
                        orderFlow.FlowOrderFid = orderId;
                        orderFlow.FlowOperFid = custFid;
                        orderFlow.FlowOrderStatusFid = (int)Dict.OrderStatus.Canceled;
                        orderFlow.FlowCreatedOn = DateTime.Now;
                        orderFlow.FlowMemo = "用户取消订单";
                        bool orderFlowUpdate = SqlServerDal.OrderFlow.Insert(orderFlow, trans);
                        if (orderFlowUpdate)
                        {
                            switch (order.OrderInsTypeFid)
                            {
                                case (int)Dict.InsType.Air:
                                    List<Model.PolicyInfoAir> policyInfoAir = new List<com.huigo.Framework.Model.PolicyInfoAir>();
                                    List<WhereStatement> where = new List<WhereStatement>()
                                    {
                                        new WhereStatement("POLICY_ORDER_FID","=",""+orderId+"","and"),
                                        new WhereStatement("POLICY_CUST_FID","=",""+custFid+"","")
                                    };
                                    List<string> fileds1 = new List<string>()
                                    {
                                        "POLICY_ID",
                                        "POLICY_STATUS"
                                    };
                                    List<Model.PolicyInfoAir> airList = SqlServerDal.PolicyInfoAir.GetItems(fileds1, null, where, null, null, trans);
                                    if (airList.Count > 0)
                                    {
                                        Model.PolicyInfoAir air = new com.huigo.Framework.Model.PolicyInfoAir();
                                        air.PolicyStatus = 0;
                                        List<WhereStatement> where1 = new List<WhereStatement>()
                                        {
                                             new WhereStatement("POLICY_ORDER_FID","=",""+orderId+"","and"),
                                             new WhereStatement("POLICY_CUST_FID","=",""+custFid+"","")
                                        };
                                        SqlServerDal.PolicyInfoAir.Update(air, where1, trans);
                                        for (int i = 0; i < airList.Count; i++)
                                        {
                                            Model.PolicyFlow policyFlow = new com.huigo.Framework.Model.PolicyFlow();
                                            policyFlow.FlowInsTypeFid = order.OrderInsTypeFid;
                                            policyFlow.FlowPolicyFid = airList[i].PolicyId;
                                            policyFlow.FlowPolicyStatusFid = (int)Dict.PolicyStatus.Canceled;
                                            policyFlow.FlowCreatedOn = DateTime.Now;
                                            policyFlow.FlowMemo = "用户取消订单";
                                            SqlServerDal.PolicyFlow.Insert(policyFlow, trans);
                                        }
                                    }
                                    break;
                                case (int)Dict.InsType.Travel:
                                    List<Model.PolicyInfoTravel> policyInfoTravel = new List<com.huigo.Framework.Model.PolicyInfoTravel>();
                                    List<WhereStatement> where_ = new List<WhereStatement>()
                                    {
                                        new WhereStatement("POLICY_ORDER_FID","=",""+orderId+"","and"),
                                        new WhereStatement("POLICY_CUST_FID","=",""+custFid+"","")
                                    };
                                    List<string> fileds2 = new List<string>()
                                    {
                                        "POLICY_ID",
                                        "POLICY_STATUS"
                                    };
                                    List<Model.PolicyInfoTravel> travelList = SqlServerDal.PolicyInfoTravel.GetItems(fileds2, null, where_, null, null, trans);
                                    if (travelList.Count > 0)
                                    {
                                        Model.PolicyInfoTravel travel = new com.huigo.Framework.Model.PolicyInfoTravel();
                                        travel.PolicyStatus = 0;
                                        List<WhereStatement> where2 = new List<WhereStatement>()
                                        {
                                            new WhereStatement("POLICY_ORDER_FID","=",""+orderId+"","and"),
                                            new WhereStatement("POLICY_CUST_FID","=",""+custFid+"","")
                                        };
                                        SqlServerDal.PolicyInfoTravel.Update(travel, where2, trans);

                                        for (int i = 0; i < travelList.Count; i++)
                                        {

                                            Model.PolicyFlow policyFlow = new com.huigo.Framework.Model.PolicyFlow();
                                            policyFlow.FlowInsTypeFid = order.OrderInsTypeFid;
                                            policyFlow.FlowPolicyFid = travelList[i].PolicyId;
                                            policyFlow.FlowOperFid = custFid;
                                            policyFlow.FlowPolicyStatusFid = (int)Dict.PolicyStatus.Canceled;
                                            policyFlow.FlowCreatedOn = DateTime.Now;
                                            policyFlow.FlowMemo = "用户取消订单";
                                            SqlServerDal.PolicyFlow.Insert(policyFlow, trans);
                                        }
                                    }
                                    break;
                            }
                            trans.Commit();
                            result = true;
                        }
                        else
                        {
                            trans.Rollback();
                        }
                    }
                    else
                    {
                        trans.Rollback();
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 查询用户订单下的保单
        /// </summary>
        /// <param name="policyNo">保单号</param>
        /// <param name="policyInsuredName">被保人姓名</param>
        /// <param name="policyInsuredNo">被保人证件号</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="type">保单类型</param>
        /// <param name="custFid">用户ID</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public static List<Model.OrderInfo> getOrderInfoPolicyList(string policyNo, string policyInsuredName, string policyInsuredNo, DateTime start, DateTime end, long type, long custFid, int pageSize, int pageIndex)
        {
            string sql = " UNION ALL SELECT ORDER_CREATED_ON,ORDER_ID,ORDER_CUST_FID,ORDER_PRODUCT_FID,ORDER_INS_COMPANY_NAME,ORDER_PRODUCT_NAME,ORDER_SUP_NAME,ORDER_INS_TYPE_FID,ORDER_NO,POLICY_ID,POLICY_NO,POLICY_INSURED_NAME,POLICY_INSURED_CREDENTIAL_NO,POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_FROM_PERIOD,POLICY_STATUS,STATUS_DESC FROM HG_ORDER_INFO INNER JOIN HG_POLICY_INFO_TRAVEL ON ORDER_ID=POLICY_ORDER_FID LEFT JOIN HG_POLICY_STATUS ON STATUS_ID=POLICY_STATUS  WHERE";
            string sql1 = "SELECT ORDER_CREATED_ON,ORDER_ID,ORDER_CUST_FID,ORDER_PRODUCT_FID,ORDER_INS_COMPANY_NAME,ORDER_PRODUCT_NAME,ORDER_SUP_NAME,ORDER_INS_TYPE_FID,ORDER_NO,POLICY_ID,POLICY_NO,POLICY_INSURED_NAME,POLICY_INSURED_CREDENTIAL_NO,POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_FROM_PERIOD,POLICY_STATUS,STATUS_DESC FROM HG_ORDER_INFO INNER JOIN HG_POLICY_INFO_AIR ON ORDER_ID=POLICY_ORDER_FID LEFT JOIN HG_POLICY_STATUS ON STATUS_ID=POLICY_STATUS  WHERE";

            if (!string.IsNullOrEmpty(policyNo))
            {
                sql += " POLICY_NO='" + policyNo + "' AND";
                sql1 += " POLICY_NO='" + policyNo + "' AND";
            }
            if (!string.IsNullOrEmpty(policyInsuredName))
            {
                sql += " POLICY_INSURED_NAME='" + policyInsuredName + "' and";
                sql1 += " POLICY_INSURED_NAME='" + policyInsuredName + "' and";
            }
            if (!string.IsNullOrEmpty(policyInsuredNo))
            {
                sql += " POLICY_INSURED_CREDENTIAL_NO='" + policyInsuredNo + "' and";
                sql1 += " POLICY_INSURED_CREDENTIAL_NO='" + policyInsuredNo + "' and";
            }
            if (start != DateTime.MinValue && end != DateTime.MinValue)
            {
                sql += " POLICY_FROM_PERIOD between '" + start + "' and '" + end + "' and";
                sql1 += " POLICY_FROM_PERIOD between '" + start + "' and '" + end + "' and";
            }
            if (type > 0)
            {
                sql += " ORDER_INS_TYPE_FID='" + type + "' and";
                sql1 += " ORDER_INS_TYPE_FID='" + type + "' and";
            }
            if (string.IsNullOrEmpty(policyNo) && string.IsNullOrEmpty(policyInsuredName) && string.IsNullOrEmpty(policyInsuredNo) && start == DateTime.MinValue && end == DateTime.MinValue && (type == 0 || type < 0))
            {
                sql += " ORDER_CREATED_ON between '" + DateTime.Now.Date + "' and '" + DateTime.Now.Date.AddDays(1) + "' and";
                sql1 += " ORDER_CREATED_ON between '" + DateTime.Now.Date + "' and '" + DateTime.Now.Date.AddDays(1) + "' and";
            }
            sql += " ORDER_CUST_FID=" + custFid + "";
            sql1 += " ORDER_CUST_FID=" + custFid + "";

            string SQL = sql1 + sql;
            List<OrderByStatement> orderList = new List<OrderByStatement>()
            {
                new OrderByStatement("ORDER_CREATED_ON",false)
            };
            List<Model.OrderInfo> orderInfoList = null;
            int recordCount = 0;
            int pageCount = 0;
            orderInfoList = SqlServerDal.OrderInfo.GetItemsUnion(SQL, orderList, "[PROC_PAGINATION_OUT_UNION]", pageSize, pageIndex, out recordCount, out pageCount, null);
            orderInfoList.ForEach(delegate(Model.OrderInfo info)
            {
                info.RecordCount = recordCount;
                info.PageCount = pageCount;
            });
            return orderInfoList;
        }
        /// <summary>
        /// 查询问题保单
        /// </summary>
        /// <param name="custFid">用户ID</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public static List<Model.OrderInfo> GetProblemPolicyList(long custFid, int pageSize, int pageIndex)
        {
            string sql = " UNION ALL SELECT ORDER_CREATED_ON,ORDER_ID,ORDER_CUST_FID,ORDER_PRODUCT_FID,ORDER_INS_COMPANY_NAME,ORDER_PRODUCT_NAME,ORDER_SUP_NAME,ORDER_INS_TYPE_FID,ORDER_NO,POLICY_ID,POLICY_NO,POLICY_INSURED_NAME,POLICY_INSURED_CREDENTIAL_NO,POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_FROM_PERIOD,POLICY_STATUS,POLICY_FEE_STATUS,STATUS_DESC FROM HG_ORDER_INFO INNER JOIN HG_POLICY_INFO_TRAVEL ON ORDER_ID=POLICY_ORDER_FID LEFT JOIN HG_POLICY_STATUS ON STATUS_ID=POLICY_STATUS  WHERE";
            string sqls = "SELECT ORDER_CREATED_ON,ORDER_ID,ORDER_CUST_FID,ORDER_PRODUCT_FID,ORDER_INS_COMPANY_NAME,ORDER_PRODUCT_NAME,ORDER_SUP_NAME,ORDER_INS_TYPE_FID,ORDER_NO,POLICY_ID,POLICY_NO,POLICY_INSURED_NAME,POLICY_INSURED_CREDENTIAL_NO,POLICY_UNIT_PRICE,POLICY_COUNT,POLICY_FROM_PERIOD,POLICY_STATUS,POLICY_FEE_STATUS,STATUS_DESC FROM HG_ORDER_INFO INNER JOIN HG_POLICY_INFO_AIR ON ORDER_ID=POLICY_ORDER_FID LEFT JOIN HG_POLICY_STATUS ON STATUS_ID=POLICY_STATUS  WHERE";

            if (custFid > 0)
            {
                sql += " ORDER_CUST_FID=" + custFid + " and";
                sqls += " ORDER_CUST_FID=" + custFid + " and";
            }
            sql += " (POLICY_STATUS in(3,8) or POLICY_FEE_STATUS =4)";
            sqls += " (POLICY_STATUS in(3,8) or POLICY_FEE_STATUS =4)";

            List<OrderByStatement> orderList = new List<OrderByStatement>()
            {
                new OrderByStatement("ORDER_CREATED_ON",false)
            };
            string SQL = sqls + sql;
            List<Model.OrderInfo> orderInfoList = null;
            int recordCount = 0;
            int pageCount = 0;
            orderInfoList = SqlServerDal.OrderInfo.GetItemsUnion(SQL, orderList, "[PROC_PAGINATION_OUT_UNION]", pageSize, pageIndex, out recordCount, out pageCount, null);
            orderInfoList.ForEach(delegate(Model.OrderInfo info)
            {
                info.RecordCount = recordCount;
                info.PageCount = pageCount;
            });
            return orderInfoList;
        }
        /// <summary>
        /// 查询问题保单的总数
        /// </summary>
        /// <param name="custFid"></param>
        /// <returns></returns>
        public static int GetProblemPolicyCount(long custFid)
        {
            List<string> filed1 = new List<string>()
            {
                "POLICY_ID"
            };
            List<WhereStatement> whereList = new List<WhereStatement>()
            {
                new WhereStatement("POLICY_CUST_FID","=",""+custFid+"","and"),
                new WhereStatement("(POLICY_STATUS","in","(3,8)","or"),
                new WhereStatement("POLICY_FEE_STATUS","=","4)","")
            };
            List<Model.PolicyInfoAir> airList = SqlServerDal.PolicyInfoAir.GetItems(filed1, null, whereList, null, null, null);
            List<Model.PolicyInfoTravel> travelList = SqlServerDal.PolicyInfoTravel.GetItems(filed1, null, whereList, null, null, null);
            int count = airList.Count + travelList.Count;
            return count;
        }
        /// <summary>
        /// 验证保单
        /// </summary>
        /// <param name="orderId">订单ID</param>
        /// <param name="custFid">用户ID</param>
        /// <param name="policyId">保单ID</param>
        /// <returns></returns>
        public static int getInsTypeId(long orderId, long custFid,long policyId)
        {
            int resutl=0;
            Model.OrderInfo orderInfo = new com.huigo.Framework.Model.OrderInfo();
            orderInfo.OrderId = orderId;
            orderInfo.OrderCustFid=custFid;
            List<string> fileds = new List<string>()
            {
                "ORDER_INS_TYPE_FID"
            };
            Model.OrderInfo order = SqlServerDal.OrderInfo.GetItem(orderInfo, fileds, null, null);
            if (order != null)
            {
                long type = order.OrderInsTypeFid;
                switch (type)
                {
                    case (int)Dict.InsType.Air:
                        Model.PolicyInfoAir air = new com.huigo.Framework.Model.PolicyInfoAir();
                        air.PolicyOrderFid = orderId;
                        air.PolicyId = policyId;
                        air.PolicyStatus = (int)Dict.PolicyStatus.InsuredSuc;
                        List<string> filed1 = new List<string>()
                        {
                            "POLICY_ID"
                        };
                        Model.PolicyInfoAir policyAir = SqlServerDal.PolicyInfoAir.GetItem(air, filed1, null, null);
                        if (policyAir != null)
                        {
                            resutl = (int)Dict.InsType.Air;
                        }
                        break;
                    case (int)Dict.InsType.Travel:
                        Model.PolicyInfoTravel travel = new com.huigo.Framework.Model.PolicyInfoTravel();
                        travel.PolicyOrderFid = orderId;
                        travel.PolicyId = policyId;
                        travel.PolicyStatus = (int)Dict.PolicyStatus.InsuredSuc;
                        List<string> filed2 = new List<string>()
                        {
                            "POLICY_ID"
                        };
                        Model.PolicyInfoTravel policyTravel = SqlServerDal.PolicyInfoTravel.GetItem(travel, filed2, null, null);
                        if (policyTravel != null)
                        {
                            resutl = (int)Dict.InsType.Travel;
                        }
                        break;
                }
            }
            return resutl;
        }
        /// <summary>
        /// 查询用户买个的保险产品
        /// </summary>
        /// <param name="custFid"></param>
        /// <returns></returns>
        public static List<Model.OrderInfo> getOrderProductName(long custFid)
        {
            List<string> fileds = new List<string>()
            {
                " distinct(PRODUCT_NAME)",
                "ORDER_PRODUCT_FID"                
            };
            List<JoinStatement> joinList = new List<JoinStatement>()
            {
                new JoinStatement("left","HG_PRODUCT_INFO","PRODUCT_ID=ORDER_PRODUCT_FID")
            };
            List<WhereStatement> whereList = new List<WhereStatement>()
            {
                new WhereStatement("ORDER_CUST_FID","=",""+custFid+"","and"),
                new WhereStatement("PRODUCT_STATUS","=",""+((int)Dict.IsActive.Yes).ToString()+"","")
            };
            return SqlServerDal.OrderInfo.GetItems(fileds, joinList, whereList, null, null, null);
        }
    }
}
